package com.demo2do.fms.appointment.entity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;
import com.demo2do.fms.appointment.utils.FormatUtils;
import com.demo2do.fms.entity.report.DriverCostAppointment;

/**
 *
 * @author Ark
 *
 */
public class DriverCostSearch implements QueryBuilder {

    /** 运输公司 */
    private Long organizationId;
    
    /** 驳运车  */
    private Long vehicleId;
    
    /** 报销开始日期 */
    private String beginReimburseDate;
    
    /** 报销结束日期 */
    private String endReimburseDate;
    
    /** 起始装卸地  */
    private Long startFacilityId;
    
    /** 目的装卸地 */
    private Long destinationFacilityId;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;

    private QueryOrder queryOrder;
    
    /**
     * The default constructor
     */
    public DriverCostSearch() {
        queryOrder = new QueryOrder("vehicle", "ASC").add("expenseReimburseDate", "ASC");
    }
    
    /**
     * construct search by appointment
     * 
     * @param appointment
     */
    public DriverCostSearch(DriverCostAppointment appointment) {
        this.organizationId = appointment.getOrganizationId();
        this.beginReimburseDate = appointment.getBeginReimburseDate();
        this.endReimburseDate = appointment.getEndReimburseDate();
        queryOrder = new QueryOrder("vehicle", "ASC").add("expenseReimburseDate", "ASC");
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("t1", "organization_id", organizationId)
                                           .addEquals("t1", "vehicle_id", vehicleId);
            
            if (StringUtils.isNotBlank(beginReimburseDate)) {
                queryFilter.addSentence("t1.expense_reimburse_date >= TO_DATE('" + beginReimburseDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endReimburseDate)) {
                queryFilter.addSentence("t1.expense_reimburse_date < TO_DATE('" + endReimburseDate.replace("-", "") + "', 'yyyymmdd') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
            queryFilter.addEquals("t1", "start_facility_id", startFacilityId)
                       .addEquals("t1", "destination_facility_id", destinationFacilityId);
                       
        }
        
        return queryFilter;
        
    }

    /**
     * build count sentence
     * 
     * @return
     */
    public String buildCountTemplate() {
        String sentence = "SELECT COUNT(*) FROM tbl_truck_dispatch_order t1, vw_vehicle t2, tbl_all_employee_info t3, tbl_all_employee_info t4, vw_load_place t5, vw_load_place t6, tbl_truck_transport_cost t7, tbl_all_user_info t8, tbl_all_user_info t9 WHERE t1.vehicle_id = t2.id AND t1.main_driver_id = t3.id(+) AND t1.first_vice_driver_id = t4.id(+) AND t1.start_facility_id = t5.id AND t1.destination_facility_id = t6.id AND t1.id = t7.dispatch_order_id AND t1.is_disabled = 0 AND t1.is_expense_reimbursed = 1 AND t1.last_operator_id = t8.id AND t1.expense_reimburse_user = t9.id" + getPreparedFilter(false).clauseSentence();
        return sentence;
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(256);
        
        buffer.append(" SELECT t2.vehicle_code || ' - ' || t2.vehicle_name AS vehicle, ")
                .append("t1.dispatch_order_code AS dispatchOrderCode, ")
                .append("t1.shipment_group_id AS shipmentGroupId, ")
                .append("t3.name AS mainDriver, ")
                .append("t4.name AS firstViceDriver, ")
                .append("t1.expense_reimburse_date AS expenseReimburseDate, ")
                .append("t1.reimburse_code AS reimburseCode, ")
                .append("t5.facility_code || ' - ' || t5.facility_name AS srcFacility, ")
                .append("t6.facility_code || ' - ' || t6.facility_name AS descFacility, ")
                .append("t1.actual_load_number AS loadNumber, ")
                .append("FC_GET_CARSUMMARY(t1.id) AS carSummary, ")
                .append("t1.facility_distance AS facilityDistance, ")
                .append("t7.road_fee AS roadFee, ")
                .append("t7.external_oil_fee AS externalOilFee, ")
                .append("t7.internal_oil_fee_compensation AS internalOilFeeCompensation, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 74902764) AS otherFee1, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 77630081) AS otherFee2, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 5372144) AS otherFee3, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 1724768) AS otherFee4, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 1724828) AS otherFee5, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 1724120) AS otherFee6, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 265989) AS otherFee7, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 1802171) AS otherFee8, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 875443) AS otherFee9, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 875454) AS otherFee10, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 1767233) AS otherFee11, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 167108) AS otherFee12, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 137655) AS otherFee13, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 167106) AS otherFee14, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 135719) AS otherFee15, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 137663) AS otherFee16, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 137669) AS otherFee17, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 137602) AS otherFee18, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 137652) AS otherFee19, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 6059342) AS otherFee20, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 74431626) AS otherFee21, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 252816353) AS otherFee22, ")
                .append("FC_GET_OTHER_FEE_DETAIL(t1.id, 10000) AS otherFee23, ")
                .append("t8.login_name AS lastModifyUser, ")
                .append("t9.login_name AS reimburseUser ")
           .append("FROM tbl_truck_dispatch_order t1, vw_vehicle t2, tbl_all_employee_info t3, tbl_all_employee_info t4, vw_load_place t5, vw_load_place t6, tbl_truck_transport_cost t7, tbl_all_user_info t8, tbl_all_user_info t9 ")
           .append("WHERE t1.vehicle_id = t2.id AND t1.main_driver_id = t3.id(+) AND t1.first_vice_driver_id = t4.id(+) AND t1.start_facility_id = t5.id AND t1.destination_facility_id = t6.id AND t1.id = t7.dispatch_order_id AND t1.is_disabled = 0 AND t1.is_expense_reimbursed = 1 AND t1.last_operator_id = t8.id AND t1.expense_reimburse_user = t9.id")
           .append(getPreparedFilter(false).clauseSentence())
           .append(queryOrder.sentence());
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
        
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<String[]> buildRowMapper() {
        
        RowMapper<String[]> rowMapper = new RowMapper<String[]>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public String[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                String vehicle = rs.getString("vehicle");
                String dispatchOrderCode = rs.getString("dispatchOrderCode");
                String shipmentGroupId = rs.getString("shipmentGroupId");
                String mainDriver = rs.getString("mainDriver");
                String firstViceDriver = rs.getString("firstViceDriver");
                String expenseReimburseDate = FormatUtils.formatDate(rs.getDate("expenseReimburseDate"));
                String reimburseCode = rs.getString("reimburseCode");
                String srcFacility = rs.getString("srcFacility");
                String descFacility = rs.getString("descFacility");
                String loadNumber = rs.getString("loadNumber");
                String carSummary = rs.getString("carSummary");
                String facilityDistance = rs.getString("facilityDistance");
                String roadFee = rs.getString("roadFee");
                String externalOilFee = rs.getString("externalOilFee");
                String internalOilFeeCompensation = rs.getString("internalOilFeeCompensation");
                String otherFee1 = rs.getString("otherFee1");
                String otherFee2 = rs.getString("otherFee2");
                String otherFee3 = rs.getString("otherFee3");
                String otherFee4 = rs.getString("otherFee4");
                String otherFee5 = rs.getString("otherFee5");
                String otherFee6 = rs.getString("otherFee6");
                String otherFee7 = rs.getString("otherFee7");
                String otherFee8 = rs.getString("otherFee8");
                String otherFee9 = rs.getString("otherFee9");
                String otherFee10 = rs.getString("otherFee10");
                String otherFee11 = rs.getString("otherFee11");
                String otherFee12 = rs.getString("otherFee12");
                String otherFee13 = rs.getString("otherFee13");
                String otherFee14 = rs.getString("otherFee14");
                String otherFee15 = rs.getString("otherFee15");
                String otherFee16 = rs.getString("otherFee16");
                String otherFee17 = rs.getString("otherFee17");
                String otherFee18 = rs.getString("otherFee18");
                String otherFee19 = rs.getString("otherFee19");
                String otherFee20 = rs.getString("otherFee20");
                String otherFee21 = rs.getString("otherFee21");
                String otherFee22 = rs.getString("otherFee22");
                String otherFee23 = rs.getString("otherFee23");
                String lastModifyUser = rs.getString("lastModifyUser");
                String reimburseUser = rs.getString("reimburseUser");
                
                String[] result = {vehicle, dispatchOrderCode, shipmentGroupId, mainDriver, firstViceDriver, expenseReimburseDate, 
                        reimburseCode, srcFacility, descFacility, loadNumber, carSummary, facilityDistance, roadFee, externalOilFee, 
                        internalOilFeeCompensation, otherFee1, otherFee2, otherFee3, otherFee4, otherFee5, otherFee6, otherFee7, otherFee8,
                        otherFee9, otherFee10, otherFee11, otherFee12, otherFee13, otherFee14, otherFee15, otherFee16, otherFee17, otherFee18, otherFee19,
                        otherFee20, otherFee21, otherFee22, otherFee23, lastModifyUser, reimburseUser};
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the vehicleId
     */
    public Long getVehicleId() {
        return vehicleId;
    }

    /**
     * @return the beginReimburseDate
     */
    public String getBeginReimburseDate() {
        return beginReimburseDate;
    }

    /**
     * @return the endReimburseDate
     */
    public String getEndReimburseDate() {
        return endReimburseDate;
    }

    /**
     * @return the startFacilityId
     */
    public Long getStartFacilityId() {
        return startFacilityId;
    }

    /**
     * @return the destinationFacilityId
     */
    public Long getDestinationFacilityId() {
        return destinationFacilityId;
    }

    /**
     * @return
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return
     */
    public Integer getEndIndex() {
        return endIndex;
    }
    
    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param vehicleId the vehicleId to set
     */
    public void setVehicleId(Long vehicleId) {
        this.vehicleId = vehicleId;
    }

    /**
     * @param beginReimburseDate the beginReimburseDate to set
     */
    public void setBeginReimburseDate(String beginReimburseDate) {
        this.beginReimburseDate = beginReimburseDate;
    }

    /**
     * @param endReimburseDate the endReimburseDate to set
     */
    public void setEndReimburseDate(String endReimburseDate) {
        this.endReimburseDate = endReimburseDate;
    }

    /**
     * @param startFacilityId the startFacilityId to set
     */
    public void setStartFacilityId(Long startFacilityId) {
        this.startFacilityId = startFacilityId;
    }

    /**
     * @param destinationFacilityId the destinationFacilityId to set
     */
    public void setDestinationFacilityId(Long destinationFacilityId) {
        this.destinationFacilityId = destinationFacilityId;
    }

    /**
     * @param beginIndex
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }
    
    /**
     * @param endIndex
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }

}